package com.example.bysj_sb.dao;

import com.example.bysj_sb.domain.Department;
import com.example.bysj_sb.domain.School;

import com.example.bysj_sb.service.SchoolService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.List;

@Repository("departmentDao")
public class DepartmentDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private SchoolService schoolService;
    //增加
    public boolean add(Department department) {
        //sql语句
        String sql = "INSERT INTO department (no,description,remarks,school_id) VALUES (?,?,?,?)";
        //执行sql语句，为预编译参数赋值，返回执行记录
        int affectedRowNum = jdbcTemplate.update(sql, department.getNo(),
                department.getDescription(),department.getRemarks(),department.getSchool().getId());
        if (affectedRowNum != 1) {
            return false;
        } else {
            return true;
        }
    }
    //通过定义内部类来返回查询结果
    public class DepartmentRowMapper implements RowMapper<Department>{
        @Override
        public Department mapRow(ResultSet resultSet, int i) throws SQLException {
            //将查询结果赋值给对象
            Department department = new Department();
            department.setId(resultSet.getInt("id"));
            department.setDescription(resultSet.getString("description"));
            department.setNo(resultSet.getString("no"));
            department.setRemarks(resultSet.getString("remarks"));
            School school = schoolService.find(resultSet.getInt("school_id"));
            department.setSchool(school);
            return department;
        }
    }
    //根据id查找
    public Department find(int id){
        //sql语句
        String sql = "SELECT * FROM department WHERE id = ?";
        //执行sql语句
        List<Department> departments = jdbcTemplate.query(sql, new Object[]{id}, new DepartmentRowMapper());
        if(!departments.isEmpty()){
            //返回集合中的第一个元素
            return departments.get(0);
        } else
            return null;
    }
   //查找全部
    public List<Department> findAll(){
        String sql = "SELECT * FROM department ";
        List<Department> departments = jdbcTemplate.query(sql, new DepartmentRowMapper());
            return departments;
    }
    //修改
    public boolean update(Department department) {
        String sql = "UPDATE department SET no = ? ,description=? WHERE id = ?";
        int affectedRowNum = jdbcTemplate.update(sql, department.getNo(), department.getDescription(), department.getId());
        if (affectedRowNum != 1) {
            return false;
        } else {
            return true;
        }
    }
    //根据id删除
    public boolean delete(int id){
        String sql = "DELETE FROM department WHERE id = ? ";
        int affectedRowNum = jdbcTemplate.update(sql,id);
        if (affectedRowNum!=1){
            return false;
        }else{
            return true;
        }
    }
}

